﻿using Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class D_g_display
    {
        public List<g_display> GetList()
        {
            //string sql = "SELECT A.g_id," +
            //            "C.group_name as group_name," +
            //            "C.project_name as project_name," +
            //            "C.project_info as project_info," +
            //            "B1.teacher_name as Teacher_1," +
            //            "B2.teacher_name as Teacher_2," +
            //            "B3.teacher_name as Teacher_3," +
            //            "B4.teacher_name as Teacher_4," +
            //            "B5.teacher_name as Teacher_5 " +
            //            "FROM g_data A " +
            //            "left JOIN Teacher B1 ON A.c1 = B1.teacher_id " +
            //            "left JOIN Teacher B2 ON A.c2 = B2.teacher_id " +
            //            "left JOIN Teacher B3 ON A.c3 = B3.teacher_id " +
            //            "left JOIN Teacher B4 ON A.c4 = B4.teacher_id " +
            //            "left JOIN Teacher B5 ON A.c5 = B5.teacher_id " +
            //            "left JOIN Stu_group C ON C.group_id = A.g_id";

            //var list = Sugar_helper.Db.Ado.SqlQuery<g_display>(sql);


            var list = Sugar_helper.Db.Ado.UseStoredProcedure().SqlQuery<g_display>("select_g_display");

            //var list = Sugar_helper.Db.Queryable < g_data, Stu_group, Teacher, Teacher, Teacher, Teacher, Teacher >
            //    ((g,sg,t1,t2,t3,t4,t5) =>new JoinQueryInfos(
            //    JoinType.Left, g.g_id == sg.group_id,
            //    JoinType.Left, g.c1 == t1.teacher_id,
            //    JoinType.Left, g.c2 == t2.teacher_id,
            //    JoinType.Left, g.c3 == t3.teacher_id,
            //    JoinType.Left, g.c4 == t4.teacher_id,
            //    JoinType.Left, g.c5 == t5.teacher_id
            //    ))
            //    .Select((g, sg, t1, t2, t3, t4, t5)=> new g_display{ g_id=g.g_id, group_name=sg.group_name, project_name=sg.project_name,
            //    Teacher_1 = t1.teacher_name, Teacher_2 = t2.teacher_name, Teacher_3=t3.teacher_name, Teacher_4= t4.teacher_name,Teacher_5=t5.teacher_name})
            //    .ToList();

            return list;
        }
    }
}
